/**
 * 数据库服务类
 * 负责所有数据库操作（增删改查）
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const config = require('../config/config');

class DatabaseService {
  constructor() {
    this.dbPath = path.resolve(__dirname, '../database/students.db');
    this.db = null;
  }

  /**
   * 连接数据库
   */
  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          console.error('数据库连接失败:', err.message);
          reject(err);
        } else {
          console.log('数据库连接成功');
          resolve();
        }
      });
    });
  }

  /**
   * 关闭数据库连接
   */
  close() {
    if (this.db) {
      this.db.close((err) => {
        if (err) {
          console.error('关闭数据库失败:', err.message);
        } else {
          console.log('数据库连接已关闭');
        }
      });
    }
  }

  /**
   * 插入学生记录
   * @param {Object} studentData - 学生数据 {name, age, major}
   * @returns {Promise<Object>} - 操作结果
   */
  insertStudent(studentData) {
    return new Promise((resolve, reject) => {
      const { name, age, major } = studentData;

      if (!name || !age || !major) {
        reject(new Error('学生姓名、年龄和专业都是必填字段'));
        return;
      }

      const sql = 'INSERT INTO students (name, age, major) VALUES (?, ?, ?)';
      this.db.run(sql, [name, age, major], function(err) {
        if (err) {
          console.error('插入学生记录失败:', err.message);
          reject(err);
        } else {
          console.log(`学生记录插入成功，ID: ${this.lastID}`);
          resolve({
            success: true,
            studentId: this.lastID,
            message: `成功添加学生 ${name}，学号: ${this.lastID}`
          });
        }
      });
    });
  }

  /**
   * 查询学生记录
   * @param {Object} conditions - 查询条件
   * @returns {Promise<Object>} - 查询结果
   */
  selectStudents(conditions = {}) {
    return new Promise((resolve, reject) => {
      let sql = 'SELECT * FROM students';
      let params = [];

      // 构建WHERE子句
      if (conditions && Object.keys(conditions).length > 0) {
        const whereClause = [];
        for (const [key, value] of Object.entries(conditions)) {
          if (['id', 'name', 'age', 'major'].includes(key)) {
            whereClause.push(`${key} = ?`);
            params.push(value);
          }
        }
        if (whereClause.length > 0) {
          sql += ' WHERE ' + whereClause.join(' AND ');
        }
      }

      sql += ' ORDER BY created_at DESC';

      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.error('查询学生记录失败:', err.message);
          reject(err);
        } else {
          console.log(`查询到 ${rows.length} 条学生记录`);
          resolve({
            success: true,
            students: rows,
            count: rows.length,
            message: `查询到 ${rows.length} 条学生记录`
          });
        }
      });
    });
  }

  /**
   * 更新学生记录
   * @param {Object} updateData - 更新数据
   * @param {Object} conditions - 更新条件
   * @returns {Promise<Object>} - 操作结果
   */
  updateStudents(updateData, conditions) {
    return new Promise((resolve, reject) => {
      if (!updateData || Object.keys(updateData).length === 0) {
        reject(new Error('更新数据不能为空'));
        return;
      }

      if (!conditions || Object.keys(conditions).length === 0) {
        reject(new Error('更新条件不能为空'));
        return;
      }

      // 构建SET子句
      const setClause = [];
      const setParams = [];
      for (const [key, value] of Object.entries(updateData)) {
        if (['name', 'age', 'major'].includes(key)) {
          setClause.push(`${key} = ?`);
          setParams.push(value);
        }
      }

      if (setClause.length === 0) {
        reject(new Error('没有有效的更新字段'));
        return;
      }

      // 构建WHERE子句
      const whereClause = [];
      const whereParams = [];
      for (const [key, value] of Object.entries(conditions)) {
        if (['id', 'name', 'age', 'major'].includes(key)) {
          whereClause.push(`${key} = ?`);
          whereParams.push(value);
        }
      }

      if (whereClause.length === 0) {
        reject(new Error('没有有效的更新条件'));
        return;
      }

      const sql = `UPDATE students SET ${setClause.join(', ')} WHERE ${whereClause.join(' AND ')}`;
      const params = [...setParams, ...whereParams];

      this.db.run(sql, params, function(err) {
        if (err) {
          console.error('更新学生记录失败:', err.message);
          reject(err);
        } else {
          console.log(`更新了 ${this.changes} 条学生记录`);
          resolve({
            success: true,
            affectedRows: this.changes,
            message: `成功更新 ${this.changes} 条学生记录`
          });
        }
      });
    });
  }

  /**
   * 删除学生记录
   * @param {Object} conditions - 删除条件
   * @returns {Promise<Object>} - 操作结果
   */
  deleteStudents(conditions) {
    return new Promise((resolve, reject) => {
      if (!conditions || Object.keys(conditions).length === 0) {
        reject(new Error('删除条件不能为空'));
        return;
      }

      // 构建WHERE子句
      const whereClause = [];
      const params = [];
      for (const [key, value] of Object.entries(conditions)) {
        if (['id', 'name', 'age', 'major'].includes(key)) {
          whereClause.push(`${key} = ?`);
          params.push(value);
        }
      }

      if (whereClause.length === 0) {
        reject(new Error('没有有效的删除条件'));
        return;
      }

      const sql = `DELETE FROM students WHERE ${whereClause.join(' AND ')}`;

      this.db.run(sql, params, function(err) {
        if (err) {
          console.error('删除学生记录失败:', err.message);
          reject(err);
        } else {
          console.log(`删除了 ${this.changes} 条学生记录`);
          resolve({
            success: true,
            affectedRows: this.changes,
            message: `成功删除 ${this.changes} 条学生记录`
          });
        }
      });
    });
  }

  /**
   * 获取数据库统计信息
   * @returns {Promise<Object>} - 统计信息
   */
  getStatistics() {
    return new Promise((resolve, reject) => {
      const queries = [
        { name: 'total', sql: 'SELECT COUNT(*) as count FROM students' },
        { name: 'majors', sql: 'SELECT major, COUNT(*) as count FROM students GROUP BY major ORDER BY count DESC' },
        { name: 'avgAge', sql: 'SELECT AVG(age) as averageAge FROM students' }
      ];

      const results = {};
      let completed = 0;

      queries.forEach(query => {
        this.db.all(query.sql, (err, rows) => {
          if (err) {
            reject(err);
            return;
          }

          if (query.name === 'total') {
            results.totalStudents = rows[0].count;
          } else if (query.name === 'majors') {
            results.majorDistribution = rows;
          } else if (query.name === 'avgAge') {
            results.averageAge = rows[0].averageAge ? Math.round(rows[0].averageAge * 10) / 10 : 0;
          }

          completed++;
          if (completed === queries.length) {
            resolve({
              success: true,
              statistics: results
            });
          }
        });
      });
    });
  }
}

module.exports = DatabaseService;